import pymysql


def residentPopulationInEachCity():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from population where type='年末常住人口数' and city!='广东省' and year=2022 LIMIT 21"
    cur.execute(sql)
    datas = cur.fetchall()
    city = [i['city'] for i in datas]
    number = [round(float(i['number']), 1) for i in datas]
    data = {'city': city, 'number': number}
    print(data)
    cur.close()
    conn.close()
    return data


def getAQICity():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    sql = "SELECT * from environment where type='AQI达标率' and city!='广东省' and year=2022"
    cur.execute(sql)
    datas = cur.fetchall()
    city = [str(i['city']).replace('\ufeff', '') for i in datas]
    number = [round(float(i['number']), 1) for i in datas]
    data = []
    for i in range(len(city)):
        data.append({'name': city[i], 'value': number[i]})
    data = {'data': data}
    print(data)
    cur.close()
    conn.close()
    return data


def getGrossDomesticProduct():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()
    years = ['2015', '2016', '2017', '2018', '2019', '2020', '2021', '2022']
    citys = ['珠海', '梅州', '东莞', '肇庆', '东翼', '潮州', '韶关', '汕尾', '山区', '湛江', '江门', '中山', '佛山',
             '深圳', '云浮', '西翼', '汕头', '阳江', '惠州', '广州', '揭阳', '河源', '清远', '茂名']
    number1 = [[] for i in range(8)]
    for i in citys:
        sql = "SELECT * from economic where type='地区生产总值' and city='{}' and year>2014 ORDER BY year".format(i)
        cur.execute(sql)
        datas = cur.fetchall()
        number = list(round(float(i['number']), 2) for i in datas)
        # print(number)
        for ind in range(len(number)):
            number1[ind].append(number[ind])

    data = {'city': citys, 'number': number1, 'year': years}
    print(number1)
    cur.close()
    conn.close()
    return data


def getSalesRevenueOfCommercialHousing():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()

    sql = "SELECT * from realestate where type='商品房销售额' and city!='广东省' and city!='珠三角' ORDER BY number desc LIMIT 5"
    cur.execute(sql)
    datas = cur.fetchall()
    city = [i['city'] for i in datas]
    number = list(round(float(i['number']), 2) for i in datas)

    data = {'city': city, 'number': number}
    print(data)
    cur.close()
    conn.close()
    return data


def getPerCapitaConsumption():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='123456', db='citydata',
                           charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)
    cur = conn.cursor()

    sql = "SELECT * from economic where type='全体居民人均消费支出' and city!='广东省' and year=2022 ORDER BY number desc LIMIT 5"
    cur.execute(sql)
    datas = cur.fetchall()
    city = [i['city'] for i in datas]
    number = list(round(float(i['number']), 2) for i in datas)
    num = []
    for i in range(len(city)):
        num.append({'name': city[i], 'value': number[i]})
    data = {'city': city, 'data': num}
    print(data)
    cur.close()
    conn.close()
    return data


if __name__ == '__main__':
    getAQICity()
